oralce 常用函数
1.单行函数
function_name(column|expression,[arg1,arg2,...])
-接收参数并返回一个结果
-每行返回一个结果
-可以改变数据类型
-能被嵌套
-可以用于SELECT,WHERE和ORDER BY子句
-包括：字符函数，数值函数，日期函数，转换函数，通用函数
2.字符函数
-包括：大小写转换函数，字符处理函数
1)大小写转换函数
函数 结果
LOWER('SQL Course') sql course
UPPER('SQL Course') SQL COURSE
INITCAP('SQL Course') Sql Course
例：
SELECT empno,ename,deptno
FROM emp
WHERE ename=UPPER('blake');
结果：
EMPNO ENAME DEPTNO
7698 BLAKE 30
2)字符串处理函数
函数 结果
CONCAT('Good','String') GoodString
SUBSTR('String',1,3) Str
LENGTH('String') 6
INSTR('String','r') 3
LPAD(sal,10,'*') ******5000
RPAD(sal,10,'*') 5000******
TRIM('S' FROM 'SSMITH') MITH
例：
SELECT ename,CONCAT(ename,job),LENGTH(ename),INSTR(ename,'A')
FROM emp
WHERE SUBSTR(job,1,5)='SALES';
结果：
ENAME CONCAT(ENAME,JOB) LENGTH(ENAME) INSTR(ENAME,'A')
ALLEN ALLENSALESMAN 5 1
WARD WARDSALESMAN 4 2
MARTIN MARTINSALESMAN 6 2
TURNER TURNERSALESMAN 6 0
3.数值函数
1) ROUND：四舍五入到指定的小数位
-ROUND(45.926,2) 45.93
例：
SELECT ROUND(45.923,2),ROUND(45.923,0),ROUND(45.923,-1)
FROM dual;
结果：
ROUND(45.923,2) ROUND(45.923,0) ROUND(45.923,-1)
45.92 46 50
2)TRUNC：截取到指定的小数位
-TURNC(45.926) 45.92
例：
SELECT TRUNC(45.923,2),TRUNC(45.923,0),TRUNC(45.923,0)
FROM dual;
结果：
TRUNC(45.923,2) TRUNC(45.923,0) TRUNC(45.923,-1)
45.92 45 40
3)MOD：取余数
MOD(1600,300) 100
例：
SELECT ename,sal,comm,MOD(sal,comm)
FROM emp
WHERE job='SALESMAN';
结果：
ENAME SAL COMM MOD(SAL,COMM)
ALLEN 1600 300 100
WARD 1250 500 250
MARTIN 1250 1400 1250
TURNER 1500 0 1500
4.日期函数
-Orale是以一种内部的数值形式存储日期的，即:世纪、年、月、日、小时、分、秒
-默认日期形式是：DD-MON-RR
-SYSDATE是一个可以返回当前系统日期和时间的函数
-DUAL是一个虚拟表用于查看SYSDATE
-对一个日期型数据加上或减去一个数可以得到一个新的日期型数据
-两个日期型数据相减得到这两个日期的间隔天数
-如要以小时相加则必须用小时数除以24得到的数据进行相加
例：
SELECT ename,(SYSDATE-hiredate)/7 WEEKS
FROM emp
WHERE deptno=10;
结果：
ENAME WEEKS
CLARK 1434.49271
KING 1411.49271
MILLER 1401.92128
5.RR日期格式
| 如果指定两位数年份是：
| 0-49 | 50-99
如果当前年份| 0-49 | 返回日期是当前世纪的日期 | 返回日期是上个世纪的日期
的两位数是：| 50-99| 返回日期是下个世纪的日期 | 返回日期是当前世纪的日期
例：
当前年份 指定的日期 RR格式 YY格式
1995 27-OCT-95 1995 1995
1995 27-OCT-17 2017 1917
2001 27-OCT-17 2017 2017
2001 27-OCT-95 1995 2095
6.日期函数
1)MONTHS_BETWEEN：两个日期之间间隔多少个月
MONTHS_BETWEEN('01-9月-95','11-9月-94')
结果：11.6774194
2)ADD_MONTHS：向一个日期数据加一定的月份
ADD_MONTHS('11-1月-94',6)
结果：11-7月 -94
3)NEXT_DAY：某个指定日期之后的一周内某天
NEXT_DAY('01-8月-07',1)
结果：05-8月 -07
注：第二个参数表示下周的第几天，周日是第一天
4)LAST_DAY：返回某月的最后一天
LAST_DAY('01-9月-95')
结果：30-9月 -95
5)ROUND：对日期进行四舍五入
ROUND('25-7月-95','MONTH')
结果：01-8月-95
ROUND('25-7月-95','YEAR')
结果：01-1月-96
6)TRUNC：对日期进行截取
TRUNC('25-7月-95','MONTH')
结果：01-7月-95
TRUNC('25-7月-95','YEAR')
结果：01-1月-95
例：
SELECT empno,hiredate,MONTHS_BETWEEN(SYSDATE,hiredate) TENURE,
ADD_MONTHS(hiredate,6) REVIEW,
NEXT_DAY(hiredate,2),LAST_DAY(hiredate)
FROM emp
WHERE MONTHS_BETWEEN(SYSDATE,hiredate)<250;
7.转换函数
-隐式数据类型转换：
赋值语句中，oralce服务器自动完成以下转换
从 到
VARCHAR2 or CHAR NUMBER
VARCHAR2 or CHAR DATE
NUMBER VARCHAR2
DATE VARCHAR2
-显式数据类型转换：
1)TO_CHAR用于日期型：TO_CHAR(date,'fmt')
日期格式模型：
-必须用单引号引起来
-可以包含任何有效的日期元素
-使用逗号与日期型数据分隔开
日期格式模型的元素：
YYYY----完整的年份数字表示
YEAR----年份的英文表示
MM------用两位数字来表示月份
MONTH---月份的英文表示
DY------用3个英文字符所写来表示星期几
DAY-----星期几完整的英文表示
例1：用时间元素格式化日期的时间部分
HH24:MI:SS:AM 15:45:32:PM
例2：通过使用双引号可以添加字符串
DD "of" MONTH 12 of 8月
例3：
SELECT ename,TO_CHAR(hiredate,'yyyy-mm-dd') HIREDATE
FROM emp
结果：
ENAME HIREDATE
SMITH 1980-12-17
ALLEN 1981-02-20
2)TO_CHAR用于数值型：TO_CHAR(number,'fmt')
通过在TO_CHAR中使用以下形式可以把数值型数据转换成变长的字符串
9------一位数字
0------显示前导零
$------显示美元符号
L------显示本地货币号
.------显示小数点
,------显示千位符
例：
SELECT TO_CHAR(sal,'$99,999') SALARY
FROM emp
WHERE ename='SCOTT';
结果：
SALARY
$3,000
3)TO_NUMBER和TO_DATE函数
-TO_NUMBER将一个字符串转换成数值型数据：TO_NUMBER(char)
-TO_DATE将一个字符串转换成日期型数据:TO_DATE(char[,'fmt'])
8.NVL函数
1)将NULL转换成为一个实际的值
-数据类型可以是date、character、number
2)数据类型必须匹配
NVL(comm,0)
NVL(hiredate,'01-7月-97')
NVL(job,'No Job Yet')
例：
SELECT ename,sal,comm,(sal*12)+NVL(comm,0)
FROM emp;
结果：
ENAME SAL COMM (SAL*12)+NVL(COMM,0)
SMITH 800 9600
ALLEN 1600 300 19500
WARD 1250 500 15500
9.DECODE函数
使用DECODE函数更便于完成诸如CASE或IF-THEN-ELSE语句的条件查询:
DECODE(col/expression,search1,result1
[,search2,result2,...,]
[,default])
例1：
SELECT job,sal,DECODE(job,'ANALYST',SAL*1.1,
'CLERK',SAL*1.15,
'MANAGER',SAL*1.20,
SAL) REVISED_SALARY
FROM emp;
结果:
JOB SAL REVISED_SALARY
CLERK 800 920
SALESMAN 1250 1250
MANAGER 2975 3570
例2：显示部门编号为30的每个雇员相应的税率
SELECT ename,sal,DECODE(TRUNC(sal/1000,0),
0,0.00,
1,0.09,
2,0.20,
3,0.30,
4,0.40,
5,0.42,
6,0.44,
0.45) TAX_RATE
FROM emp
WHERE deptno=30;
结果：
ENAME SAL TAX_RATE
ALLEN 1600 .09
WARD 1250 .09
MARTIN 1250 .09
BLAKE 2850 .2
TURNER 1500 .09
JAMES 950 0
10.函数的嵌套：
例：
SELECT ename, NVL(TO_CHAR(mgr),'No Manager')
FROM emp
WHERE mgr IS NULL;
结果：
ENAME NVL(TO_CHAR(MGR),'NOMANAGER')
KING No Manager
练习
1.查询并显示雇员名。要求首字母大写，其他字母小写，显示名字的长度，只选择雇员名开始字母是J、A或M的雇员。用雇员名排序结果
SELECT INITCAP(ename),LENGTH(ename)
FROM emp
WHERE ename LIKE 'J%'
OR ename LIKE 'A%'
OR ename LIKE 'M%';
2.查询所有雇员名和薪水。薪水格式化为15个字符长度，用$左填充，列标签SALARY
SELECT ename,LPAD(sal,15,'$') SALARY
FROM emp
3.显示姓名以N结尾的雇员
SELECT *
FROM emp
WHERE UPPER(ename) LIKE '%N';
4.查询当前日期，列标题显示为Date
SELECT TO_CHAR(SYSDATE,'DD-MONTH-YYYY') "Date"
FROM dual;
5.对每一个雇员，显示其名字，并计算从雇员受雇日期到今天的月数，列标签MONTHS_WORKED。按受雇月数排序结果，四舍五入月数到最靠近的整数月
SELECT ename,ROUND(MONTHS_BETWEEN(SYSDATE,hiredate)) MONTHS_WORKED
FROM emp;
6.查询雇员名和佣金。若雇员没有佣金，显示"No Commission"，列标签COMM
SELECT ename,decode(comm,
NULL,'No Commission',
comm) COMM
FROM emp;
或：
SELECT ename,NVL(to_char(comm),'No Commission') COMM
FROM emp;
7.显示雇员雇佣期满6个月后下一个星期五的日期。显示格式如02-4-1982，并按雇佣日期排序
SELECT TO_CHAR(NEXT_DAY(ADD_MONTHS(hiredate,6),6),'DD-Month-YYYY')
FROM emp
ORDER BY hiredate;
